create or replace function  safe_insert_binary(
    binaries.md5%type, binaries.sha512%type, binaries.filetype%type, binaries.filesize%type, binaries.stored%type,
    out ret_id binaries.id%type, out ret_inserted bool, out ret_stored binaries.stored%type
  ) as $$
begin
  begin
    insert into binaries (md5, sha512, filetype, filesize, stored) values ($1, $2, $3, $4, $5) returning id, stored into ret_id, ret_stored;
    exception
      when unique_violation then
        begin
          select id, stored into ret_id, ret_stored from binaries where md5 = $1;
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;


create or replace function update_binary_01_seen_count() returns trigger as $$
begin
  --if (TG_OP = 'INSERT') then
  update binaries set seen_count = seen_count + 1
    where id = NEW.binary_id;
  --end if;
  return NEW;
end;
$$ language plpgsql;


create or replace function update_binary_02_seen_times() returns trigger as $$
begin
  --if (TG_OP = 'INSERT') then
  update binaries set seen_first = NEW.submitted_time
    where NEW.submitted_time < seen_first and id = NEW.binary_id;
  update binaries set seen_last = NEW.submitted_time
    where NEW.submitted_time > seen_last and id = NEW.binary_id;
  --end if;
  return NEW;
end;
$$ language plpgsql;


drop trigger if exists  update_binary_01_seen_count ON attacks_submithttp;
create trigger update_binary_01_seen_count after insert on attacks_submithttp
for each row
execute procedure update_binary_01_seen_count();

drop trigger if exists  update_binary_02_seen_times ON attacks_submithttp;
create trigger update_binary_02_seen_times after insert on attacks_submithttp
for each row
execute procedure update_binary_02_seen_times();


create or replace function  safe_insert_ip(
    ips.ip%type, out ret_inserted bool
  ) as $$
begin
  begin
    insert into ips (ip) values ($1);
    exception
      when unique_violation then
        begin
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;